﻿using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Microsoft.AspNetCore.Http;
using Simple.Services.System;
using System.Data;
using OfficeOpenXml;
using System.Net;
using System.Net.Http.Headers;
using Simple.Services.System.QHSEDanger.Models.AnalysisInput;
using Simple.Services.System.QHSEViolationCategory.Models;
using System.Globalization;
using System.ComponentModel.Design;
using System.Collections;
using System.Security.Cryptography;
using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Drawing.Printing;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
using System.Reactive;
using Minio.DataModel;
using System.Reflection;
using Simple.Services.System.QHSEXyViolation.Models;
using Simple.Repository.Extensions;

namespace Simple.Services;

public class QHSEXyViolationService
{
    private readonly IDbConfig _dbConfig;
    private readonly SimpleDbContext _context;
    private readonly CacheService _cacheService;
    private readonly QHSEOrganizationService _organizationService;
    private readonly QHSEViolationCategoryService _violationCategoryService;
    private readonly QHSEKeyWorkSpecialtyService _keyWorkSpecialtyService;
    private readonly QHSEDictionaryService _dictionaryService;
    private readonly QHSEViolationHSEService _violationHSEService;
    private readonly QHSEXyViolationTypeService _violationTypeService;
    private readonly QHSEKeyWorkTypeService _keyWorkTypeService;
    private readonly QHSELocationService _locaitionService;
    private readonly QHSEViolationUserService _violationUserService;
    private readonly IQhseFileService _qhseFileService;

    public QHSEXyViolationService(IDbConfig dbConfig, SimpleDbContext context, CacheService cacheService, QHSEOrganizationService organizationService, QHSEViolationCategoryService violationCategoryService, QHSEKeyWorkSpecialtyService keyWorkSpecialtyService, QHSEDictionaryService dictionaryService, QHSEViolationHSEService violationHSEService, QHSEXyViolationTypeService violationTypeService, QHSEKeyWorkTypeService keyWorkTypeService, QHSELocationService locaitionService,QHSEViolationUserService violationUserService,IQhseFileService qhseFileService)
    {
        _dbConfig = dbConfig;
        _context = context;
        _cacheService = cacheService;
        _organizationService = organizationService;
        _violationCategoryService = violationCategoryService;
        _keyWorkSpecialtyService = keyWorkSpecialtyService;
        _dictionaryService = dictionaryService;
        _violationHSEService = violationHSEService;
        _violationTypeService = violationTypeService;
        _keyWorkTypeService = keyWorkTypeService;
        _locaitionService = locaitionService;
        _violationUserService = violationUserService;
        _qhseFileService = qhseFileService;
    }
    public async Task<List<QHSEXyViolationModel>> GetBySqlAsync(IdInputModel id)
    {
        var violations = await _context.Set<QHSESysXyViolation>().FromSqlRaw(id.Id).ToListAsync();
        var models = MapperHelper.Map<List<QHSEXyViolationModel>>(violations);
        return models;
    }
    public async Task<List<QHSEXyViolationModel>> GetAsync()
    {
        var violations = await _context.Set<QHSESysXyViolation>().ToListAsync();
        var models = MapperHelper.Map<List<QHSEXyViolationModel>>(violations);
        foreach (var model in models)
        {
            if (model.Pic != null)
            {
                var files = await utilService.GetFilesAsync(Directory.GetCurrentDirectory() + @"/VIOLATION/" + model.Pic,_qhseFileService);
                model.Files = files;
            }
        }
        return models;
    }
    public async Task<PageResultModel<QHSEXyViolationModel>> GetPageAsync(ViolationPageInputModel input)
    {
        var result = new PageResultModel<QHSEXyViolationModel>();

        if (!string.IsNullOrEmpty(input.OrganizationName) || !string.IsNullOrEmpty(input.UserName) || input.TimeOn != null || input.TimeEnd != null)
        {
            // 优化后的查询
            var query = _context.Set<QHSESysXyViolation>()
                .AsNoTracking()
                .Where(u => u.CheckDate >= input.TimeOn&&
                             u.CheckDate <= input.TimeEnd &&
                            u.Check_Type == input.CheckType)
                .AsQueryable();

            if (!string.IsNullOrEmpty(input.OrganizationName))
            {
                var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
                if (org != null)
                {
                    var escapedNamePath = EscapeLikePattern(org.NamePath);
                    query = query
                        .Join(_context.Set<QHSESysOrganization>(),
                            violation => violation.OrganizationId,
                            organization => organization.OrganizationId,
                            (violation, organization) => new { Violation = violation, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                        .Select(joinResult => joinResult.Violation);
                }
            }

            if (!string.IsNullOrEmpty(input.UserName))
            {
                query = query.Where(u => EF.Functions.Like(u.UserName, $"%{input.UserName}%"));
            }

            var totalRows = await query.CountAsync();

            var violations = await query
                .Join(_context.Set<QHSESysOrganization>(),
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new
                    {
                        Violation = violation,
                        OrganizationName = organization.NamePath
                    })
                .Select(joinResult => new QHSEXyViolationModel
                {
                    QhseXyViolationId = joinResult.Violation.QhseXyViolationId,
                    Check_Type = joinResult.Violation.Check_Type,
                    UserId = joinResult.Violation.UserId,
                    UserName = joinResult.Violation.UserName,
                    CheckDate = (DateTime)joinResult.Violation.CheckDate,
                    OrganizationId = joinResult.Violation.OrganizationId,
                    OrganizationName = joinResult.OrganizationName,
                    Description = joinResult.Violation.Description,
                    WorkItem = joinResult.Violation.WorkItem,
                    ViolationUserId = joinResult.Violation.ViolationUserId,
                    ViolationUserName = joinResult.Violation.ViolationUserName,
                    ViolationType = joinResult.Violation.ViolationType,
                    CheckOrganizationId = joinResult.Violation.CheckOrganizationId
                })
                .OrderByDescending(u => u.CheckDate)
                .Skip((input.PageNo - 1) * input.PageSize)
                .Take(input.PageSize)
                .ToListAsync();

            result = new PageResultModel<QHSEXyViolationModel>
            {
                TotalRows = totalRows,
                Rows = violations,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };

            result.SetPage(input);
            result.CountTotalPage();
        }

        return result;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<QHSEXyViolationModel> QueryDetailAsyncSQL(string violationId)
    {
        using (var connection = new OracleConnection(_dbConfig.GetConnectionString()))
        {
            connection.Open();

            // 使用参数化的 SQL 语句
            string query = "SELECT * FROM QHSE_XY_VIOLATION WHERE QHSE_XY_VIOLATION_ID =:ViolationId";

            using (var command = new OracleCommand(query, connection))
            {
                // 添加参数
                command.Parameters.Add(new OracleParameter("ViolationId", violationId));

                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var model = new QHSEXyViolationModel
                        {
                            QhseXyViolationId = reader.IsDBNull(reader.GetOrdinal("QHSE_XY_VIOLATION_ID")) ? "" : reader.GetString(reader.GetOrdinal("QHSE_XY_VIOLATION_ID")),
                            Check_Type = reader.IsDBNull(reader.GetOrdinal("CHECK_TYPE")) ? "" : reader.GetString(reader.GetOrdinal("CHECK_TYPE")),
                            CheckOrganizationId = reader.IsDBNull(reader.GetOrdinal("CHECKORGANIZATIONID")) ? "" : reader.GetString(reader.GetOrdinal("CHECKORGANIZATIONID")),
                            UserId = reader.IsDBNull(reader.GetOrdinal("USER_ID")) ? "" : reader.GetString(reader.GetOrdinal("USER_ID")),
                            UserName = reader.IsDBNull(reader.GetOrdinal("CHECK_NAME")) ? "" : reader.GetString(reader.GetOrdinal("CHECK_NAME")),
                            CheckDate = reader.IsDBNull(reader.GetOrdinal("CHECK_DATE")) ? default : reader.GetDateTime(reader.GetOrdinal("CHECK_DATE")),
                            EnterTime = reader.IsDBNull(reader.GetOrdinal("ENTER_DATE")) ? (DateTime?)null : reader.GetDateTime(reader.GetOrdinal("ENTER_DATE")),
                            EnterSys = reader.IsDBNull(reader.GetOrdinal("ENTER_SYS")) ? "" : reader.GetString(reader.GetOrdinal("ENTER_SYS")),
                            OrganizationId = reader.IsDBNull(reader.GetOrdinal("ORGANIZATION_ID")) ? "" : reader.GetString(reader.GetOrdinal("ORGANIZATION_ID")),
                            OrganizationName = reader.IsDBNull(reader.GetOrdinal("ORGANIZATION_NAME")) ? "" : reader.GetString(reader.GetOrdinal("ORGANIZATION_NAME")),
                            WorkItem = reader.IsDBNull(reader.GetOrdinal("WORK_ITEM")) ? "" : reader.GetString(reader.GetOrdinal("WORK_ITEM")),
                            WorkSpecialty = reader.IsDBNull(reader.GetOrdinal("WORK_SPECIALTY")) ? "" : reader.GetString(reader.GetOrdinal("WORK_SPECIALTY")),
                            WorkType = reader.IsDBNull(reader.GetOrdinal("WORK_TYPE")) ? "" : reader.GetString(reader.GetOrdinal("WORK_TYPE")),
                            WorkContent = reader.IsDBNull(reader.GetOrdinal("WORK_CONTENT")) ? "" : reader.GetString(reader.GetOrdinal("WORK_CONTENT")),
                            WorkRisk = reader.IsDBNull(reader.GetOrdinal("WORK_RISK")) ? "" : reader.GetString(reader.GetOrdinal("WORK_RISK")),
                            Location = reader.IsDBNull(reader.GetOrdinal("LOCATION")) ? "" : reader.GetString(reader.GetOrdinal("LOCATION")),
                            ViolationUserId = reader.IsDBNull(reader.GetOrdinal("VIOLATION_USER_ID")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATION_USER_ID")),
                            ViolationUserName = reader.IsDBNull(reader.GetOrdinal("VIOLATION_USER_NAME")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATION_USER_NAME")),
                            ViolationUserDepart = reader.IsDBNull(reader.GetOrdinal("VIOLATION_USER_DEPART")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATION_USER_DEPART")),
                            Erp = reader.IsDBNull(reader.GetOrdinal("VIOLATION_USER_ERP")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATION_USER_ERP")),
                            Position = reader.IsDBNull(reader.GetOrdinal("POSITION")) ? "" : reader.GetString(reader.GetOrdinal("POSITION")),
                            Age = reader.IsDBNull(reader.GetOrdinal("AGE")) ? "" : reader.GetString(reader.GetOrdinal("AGE")),
                            WorkYears = reader.IsDBNull(reader.GetOrdinal("WORK_YEARS")) ? "" : reader.GetString(reader.GetOrdinal("WORK_YEARS")),
                            EmployeeType = reader.IsDBNull(reader.GetOrdinal("EMPLOYEE_TYPE")) ? "" : reader.GetString(reader.GetOrdinal("EMPLOYEE_TYPE")),
                            Score = reader.IsDBNull(reader.GetOrdinal("SCORE")) ? 0f : reader.GetFloat(reader.GetOrdinal("SCORE")),
                            Fine = reader.IsDBNull(reader.GetOrdinal("FINE")) ? 0f : reader.GetFloat(reader.GetOrdinal("FINE")),
                            ViolationTypeCategory = reader.IsDBNull(reader.GetOrdinal("VIOLATION_TYPE_CATEGORY")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATION_TYPE_CATEGORY")),
                            ViolationType = reader.IsDBNull(reader.GetOrdinal("VIOLATION_TYPE")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATION_TYPE")),
                            ViolationClause = reader.IsDBNull(reader.GetOrdinal("VIOLATION_CLAUSE")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATION_CLAUSE")),
                            Description = reader.IsDBNull(reader.GetOrdinal("DESCRIPTION")) ? "" : reader.GetString(reader.GetOrdinal("DESCRIPTION")),
                            ViolationCategory = reader.IsDBNull(reader.GetOrdinal("VIOLATION_CATEGORY")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATION_CATEGORY")),
                            HseElement = reader.IsDBNull(reader.GetOrdinal("HSE_ELEMENT")) ? "" : reader.GetString(reader.GetOrdinal("HSE_ELEMENT")),
                            ObserveComm = reader.IsDBNull(reader.GetOrdinal("OBSERVE_COMM")) ? "" : reader.GetString(reader.GetOrdinal("OBSERVE_COMM")),
                            CauseAnalysis = reader.IsDBNull(reader.GetOrdinal("CAUSE_ANALYSIS")) ? "" : reader.GetString(reader.GetOrdinal("CAUSE_ANALYSIS")),
                            DirectDepartment = reader.IsDBNull(reader.GetOrdinal("DIRECT_DEPARTMENT")) ? "" : reader.GetString(reader.GetOrdinal("DIRECT_DEPARTMENT")),
                            PotentialConsequence = reader.IsDBNull(reader.GetOrdinal("POTENTIAL_CONSEQUENCE")) ? "" : reader.GetString(reader.GetOrdinal("POTENTIAL_CONSEQUENCE")),
                            Pic = reader.IsDBNull(reader.GetOrdinal("PIC")) ? "" : reader.GetString(reader.GetOrdinal("PIC")),
                            ImageHash = reader.IsDBNull(reader.GetOrdinal("IMAGE_HASH")) ? "" : reader.GetString(reader.GetOrdinal("IMAGE_HASH")),
                            ViolationCateId = reader.IsDBNull(reader.GetOrdinal("VIOLATIONCATE_ID")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATIONCATE_ID")),
                            CheckTypeCode = reader.IsDBNull(reader.GetOrdinal("CHECKTYPE_CODE")) ? "" : reader.GetString(reader.GetOrdinal("CHECKTYPE_CODE")),
                            KeyWorkCode = reader.IsDBNull(reader.GetOrdinal("KEYWORK_CODE")) ? "" : reader.GetString(reader.GetOrdinal("KEYWORK_CODE")),
                            WorkSpecialtyCode = reader.IsDBNull(reader.GetOrdinal("WORKSPECIALTY_CODE")) ? "" : reader.GetString(reader.GetOrdinal("WORKSPECIALTY_CODE")),
                            ViolationTypeCode = reader.IsDBNull(reader.GetOrdinal("VIOLATIONTYPE_CODE")) ? "" : reader.GetString(reader.GetOrdinal("VIOLATIONTYPE_CODE")),
                            HseCode = reader.IsDBNull(reader.GetOrdinal("HSE_CODE")) ? "" : reader.GetString(reader.GetOrdinal("HSE_CODE")),
                            DepartmentCode = reader.IsDBNull(reader.GetOrdinal("DEPARTMENT_CODE")) ? "" : reader.GetString(reader.GetOrdinal("DEPARTMENT_CODE")),
                            ClauseAnalysisCode = reader.IsDBNull(reader.GetOrdinal("CLAUSEANALYSIS_CODE")) ? "" : reader.GetString(reader.GetOrdinal("CLAUSEANALYSIS_CODE")),
                            EmployeeTypeCode = reader.IsDBNull(reader.GetOrdinal("EMPLOYEETYPE_CODE")) ? "" : reader.GetString(reader.GetOrdinal("EMPLOYEETYPE_CODE")),
                            WorkYearsCode = reader.IsDBNull(reader.GetOrdinal("WORKYEARS_CODE")) ? "" : reader.GetString(reader.GetOrdinal("WORKYEARS_CODE")),
                            AgeCode = reader.IsDBNull(reader.GetOrdinal("AGE_CODE")) ? "" : reader.GetString(reader.GetOrdinal("AGE_CODE")),
                            ConsequencesCode = reader.IsDBNull(reader.GetOrdinal("CONSEQUENCES_CODE")) ? "" : reader.GetString(reader.GetOrdinal("CONSEQUENCES_CODE")),
                            PositionCode = reader.IsDBNull(reader.GetOrdinal("POSITION_CODE")) ? "" : reader.GetString(reader.GetOrdinal("POSITION_CODE")),
                            LocationCode = reader.IsDBNull(reader.GetOrdinal("LOCATION_CODE")) ? "" : reader.GetString(reader.GetOrdinal("LOCATION_CODE")),
                            SecondOrganizationId = reader.IsDBNull(reader.GetOrdinal("SECOND_ORGANIZATIONID")) ? "" : reader.GetString(reader.GetOrdinal("SECOND_ORGANIZATIONID")),
                            ThirdOrganizationId = reader.IsDBNull(reader.GetOrdinal("THIRD_ORGANIZATIONID")) ? "" : reader.GetString(reader.GetOrdinal("THIRD_ORGANIZATIONID")),
                            UpdateBy = reader.IsDBNull(reader.GetOrdinal("UPDATE_BY")) ? "" : reader.GetString(reader.GetOrdinal("UPDATE_BY")),
                            UpdateTime = reader.IsDBNull(reader.GetOrdinal("UPDATE_TIME")) ? (DateTime?)null : reader.GetDateTime(reader.GetOrdinal("UPDATE_TIME")),
                            TimesViolation = reader.IsDBNull(reader.GetOrdinal("TIMES_VIOLATION")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("TIMES_VIOLATION")),
                            Management = reader.IsDBNull(reader.GetOrdinal("MANAGEMENT")) ? "" : reader.GetString(reader.GetOrdinal("MANAGEMENT")),
                            VideoUrl = reader.IsDBNull(reader.GetOrdinal("VIDEO_URL")) ? "" : reader.GetString(reader.GetOrdinal("VIDEO_URL")),
                            InspectionMethod = reader.IsDBNull(reader.GetOrdinal("INSPECTION_METHOD")) ? "" : reader.GetString(reader.GetOrdinal("INSPECTION_METHOD"))
                        };


                        // 如果有图片路径，则加载文件
                        if (!string.IsNullOrEmpty(model.Pic))
                        {
                            var violationFiles = await utilService.GetFilesAsync(model.Pic, _qhseFileService);
                            model.Files = violationFiles;
                        }

                        return model;
                    }
                    else
                    {
                        // 如果没有找到记录，则返回 null
                        return null;
                    }
                }
            }
        }
    }

    public async Task<QHSEXyViolationModel> QueryDetailAsync(string Vid)
    {
        var violation = new QHSESysXyViolation();
        violation = await _context.Set<QHSESysXyViolation>().FindAsync(Vid);
        var model = MapperHelper.Map<QHSEXyViolationModel>(violation);
        if (model.Pic != null)
        {
            var files = await utilService.GetFilesAsync(model.Pic,_qhseFileService);
            model.Files = files;
        }
        return model;
    }

    public async Task<QHSEXyViolationModel> QueryDetailCacheAsync(string Vid)
    {
        var str = HashHelper.DecryptAndExtractIdAndTime(Vid, "gmh");
        if (DateTime.Now >= str.time.AddMinutes(120))
        {
            return null;
        }
        var violation = new QHSESysXyViolation();
        violation = await _context.Set<QHSESysXyViolation>().FindAsync(str.id);
        var model = MapperHelper.Map<QHSEXyViolationModel>(violation);
        if (model.Pic != null)
        {
            var files = await utilService.GetFilesAsync(model.Pic, _qhseFileService);
            model.Files = files;
        }
        return model;
    }
    public async Task<List<(QHSEXyViolationModel models,string res)>> AddBatchAsync(List<QHSEXyViolationModel> model)
    {
        List<(QHSEXyViolationModel models, string res)> reses = new List<(QHSEXyViolationModel models, string res)>();
        int count = 0;
        foreach (var modelItem in model)
        {
            count++;
            modelItem.EnterTime = DateTime.Now;
            var res = await Add1Async(modelItem);
            if(res == "请完善信息")
            {
                reses.Add((modelItem, res));
            }
            else
            {
                reses.Add((modelItem, res));
            }
            
        }
        await _cacheService.ClearViolationCacheAsync();
        return reses;
    }
    static string RemoveHyphens(Guid guid)
    {
        return guid.ToString().Replace("-", ""); // 通过 Replace 函数移除连字符 "-"
    }
    public async Task<string> AddAsync(QHSEXyViolationModel model)
    {
        using (var transaction = await _context.Database.BeginTransactionAsync())
        {
            try
            {
                if (model.Files.Count != 0)
                {
                    var hashList = new List<string>(); // 用于存储每个文件的哈希值

                    foreach (var file in model.Files)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    model.ImageHash = string.Join("+", hashList); // 拼接哈希值字符串
                    var msg = await utilService.UploadFilesAsync("VIOLATION", model.Files, model.UserId,_qhseFileService);
                    model.Pic = msg.LastOrDefault().ArgumentName ;
                }
                if (string.IsNullOrEmpty(model.QhseXyViolationId))
                {
                    Guid guid = Guid.NewGuid();
                    string guidString = RemoveHyphens(guid);

                    model.QhseXyViolationId = guidString;
                }
                var violation = MapperHelper.Map<QHSESysXyViolation>(model);

                var userModel = await _violationUserService.GetById(new IdInputModel { Id = violation.ViolationUserId }); 
                QHSESysViolationUser user = new QHSESysViolationUser
                {
                    ViolationUserId = model.ViolationUserId,
                    ViolationUserDepart = model.ViolationUserDepart,
                    Position = model.Position,
                    Age = model.Age,
                    WorkYears = model.WorkYears,
                    EmployeeType = model.EmployeeType,
                    Erp = model.Erp
                }; 
                if (string.IsNullOrEmpty(userModel.ViolationUserId))
                {
                    
                    await _context.AddAsync(user);
                }
                else
                {
                    _context.Update(user);
                }
                violation.EnterTime = DateTime.Now;
                await _context.AddAsync(violation);
                await _context.SaveChangesAsync();

                _context.ChangeTracker.Clear();

                // Retrieve the newly inserted record within the same transaction
                //var latestViolation = await _context.Set<QHSESysXyViolation>()
                //    .Where(u=>u.UserId == violation.UserId)
                //    .OrderByDescending(x => Convert.ToInt32(x.QhseXyViolationId))
                //    .Select(u=>u.QhseXyViolationId)
                //    .FirstOrDefaultAsync();

                // Clear cache
                await _cacheService.ClearViolationCacheAsync();

                await transaction.CommitAsync(); // Commit the transaction

                if (model.Push && model.ViolationType != "轻微行为违章")
                {
                    var msg = new DangerMsg();
                    msg.Id = model.QhseXyViolationId;
                    msg.OrgId = model.SecondOrganizationId;
                    msg.Time = model.EnterTime;
                    msg.Type = model.ViolationType;
                    utilService.PushDanger(msg);
                }
                return model.QhseXyViolationId;
            }
            catch (Exception)
            {
                await transaction.RollbackAsync(); // Rollback the transaction if an exception occurs
                throw;
            }
        }
    }
    public async Task<string> Add1Async(QHSEXyViolationModel model)
    {
        using (var transaction = await _context.Database.BeginTransactionAsync())
        {
            try
            {
                if (model.Files.Count != 0)
                {
                    var hashList = new List<string>(); // 用于存储每个文件的哈希值

                    foreach (var file in model.Files)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    model.ImageHash = string.Join("+", hashList); // 拼接哈希值字符串
                    var msg = await utilService.UploadFilesAsync("VIOLATION", model.Files, model.UserId, _qhseFileService);
                    model.Pic = msg.LastOrDefault().ArgumentName;
                }
                if (string.IsNullOrEmpty(model.QhseXyViolationId))
                {
                    Guid guid = Guid.NewGuid();
                    string guidString = RemoveHyphens(guid);

                    model.QhseXyViolationId = guidString;
                }
                var violation = MapperHelper.Map<QHSESysXyViolation>(model);
                if (IsEmpty(violation))
                {
                    return "请完善信息";
                }
                if (string.IsNullOrEmpty(violation.SecondOrganizationId))
                {
                    var login = await _context.Set<QHSESysLogin>().FindAsync(violation.UserId);
                    if (login != null)
                    {
                        violation.SecondOrganizationId = login.SecondOrganizationId;
                        violation.ThirdOrganizationId = login.ThirdOrganizationId;
                    }
                }
                await _context.AddAsync(violation);
                var userModel = await _violationUserService.GetById(new IdInputModel { Id = violation.ViolationUserId });
                QHSESysViolationUser user = new QHSESysViolationUser
                {
                    ViolationUserId = model.ViolationUserId,
                    ViolationUserDepart = model.ViolationUserDepart,
                    Position = model.Position,
                    Age = model.Age,
                    WorkYears = model.WorkYears,
                    EmployeeType = model.EmployeeType,
                    Erp = model.Erp
                };
                if (string.IsNullOrEmpty(userModel.ViolationUserId))
                {

                    await _context.AddAsync(user);
                }
                else
                {
                    _context.Update(user);
                }
                await _context.SaveChangesAsync();
                _context.ChangeTracker.Clear();
                // Retrieve the newly inserted record within the same transaction
                //var latestViolation = await _context.Set<QHSESysXyViolation>()
                //    .Where(u => u.UserId == violation.UserId)
                //    .OrderByDescending(x => Convert.ToInt32(x.QhseXyViolationId))
                //    .FirstOrDefaultAsync();

                // Clear cache
                await _cacheService.ClearViolationCacheAsync();

                await transaction.CommitAsync(); // Commit the transaction

                return model.QhseXyViolationId;
            }
            catch (Exception)
            {
                await transaction.RollbackAsync(); // Rollback the transaction if an exception occurs
                throw;
            }
        }
    }
    public static bool IsEmpty(object obj)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();

        foreach (PropertyInfo property in properties)
        {
            object value = property.GetValue(obj);
            var s = property.ToString();
            if (property.ToString() != "System.String QhseXyViolationId" && property.ToString() != "System.String Pic" &&  property.ToString() != "System.String ImageHash" && property.ToString() != "System.String WorkType" && property.ToString() != "System.String WorkContent" &&
                property.ToString() != "System.String WorkRisk" && property.ToString() != "System.String KeyWorkCode" && property.ToString() != "System.String Management" && property.ToString() != "System.String VideoUrl"
                && property.ToString() != "System.String SecondOrganizationId" && property.ToString() != "System.String UpdateBy" && property.ToString() != "System.Nullable`1[System.DateTime] UpdateTime"
                 && property.ToString() != "System.String ThirdOrganizationId" && property.ToString() != "System.String InspectionMethod"
                 && property.ToString() != "System.String WorkYearsCode" && (value == null || string.IsNullOrEmpty(value.ToString())))
            {
                return true;
            }
        }

        return false;
    }
    public async Task<bool> IsImageDuplicate(string newImageBase64)
    {
        if (string.IsNullOrEmpty(newImageBase64))
        {
            return false;
        }

        using (var sha256 = SHA256.Create())
        {
            byte[] byteArray = Convert.FromBase64String(newImageBase64);
            byte[] hash = sha256.ComputeHash(byteArray);
            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();

            DateTime oneYearAgo = DateTime.Today.AddYears(-1);

            var duplicate = await _context.Set<QHSESysXyViolation>()
                .Where(violation => violation.CheckDate >= oneYearAgo && violation.ImageHash.Contains(hashString))
                .AnyAsync();

            return duplicate;
        }
    }

    public async Task<int> UpdateAsync(QHSEXyViolationModel model)
    {
        var violation = await _context.Set<QHSESysXyViolation>()
            .FindAsync(model.QhseXyViolationId);

        if (violation == null)
        {
            throw AppResultException.Status404NotFound("找不到违章，更新失败");
        }

        if (model.Files.Count != 0)
        {
            var fileUploadItems = model.Files.Select(file => new FileUploadItem
            {
                Name = file.Name,
                Base64Content = file.Base64Content
            }).ToList();
            var msgs = await utilService.UploadFilesAsync("VIOLATION",fileUploadItems, violation.UserId,_qhseFileService);
            bool uploadSuccess = msgs.All(msg => msg.FileStatus == 1);

            if (uploadSuccess)
            {
                if (!string.IsNullOrEmpty(violation.Pic))
                {
                    List<string> namesList = violation.Pic.Split(", ")
                                     .Select(name => name.Trim())  // 去除每个名字前后的空格
                                     .ToList();
                    foreach(var name in namesList)
                    {
                        _qhseFileService.RemoveAsync("ssew", name);
                    }
                }

                model.Pic = msgs.LastOrDefault()?.FileName;
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(violation.Pic))
            {
                    List<string> namesList = violation.Pic.Split(", ")
                                            .Select(name => name.Trim())  // 去除每个名字前后的空格
                                            .ToList();
                    foreach (var name in namesList)
                    {
                        _qhseFileService.RemoveAsync("ssew", name);
                    }
            }

            model.Pic = null;
        }

        MapperHelper.Map<QHSEXyViolationModel, QHSESysXyViolation>(model, violation);
        _context.Update(violation);
        //清缓存
        await _cacheService.ClearViolationCacheAsync();
        int ret = await _context.SaveChangesAsync();

        if (ret == 0)
        {
            throw AppResultException.Status200OK("更新记录数为0");
        }

        return ret;
    }
    public async Task<int> UpdateManagementAsync(QHSEXyViolationManagementModel model)
    {
        var violation = await _context.Set<QHSESysXyViolation>()
            .FindAsync(model.QhseXyViolationId);

        if (violation == null)
        {
            throw AppResultException.Status404NotFound("找不到违章，更新失败");
        }
        violation.Management = model.Management;
        _context.Update(violation);
        //清缓存
        await _cacheService.ClearViolationCacheAsync();
        int ret = await _context.SaveChangesAsync();

        if (ret == 0)
        {
            throw AppResultException.Status200OK("更新记录数为0");
        }

        return ret;
    }
    public async Task<int> DeleteAsync(IEnumerable<string> ids)
    {
        var violations = await _context.Set<QHSESysXyViolation>()
            .Where(a => ids.Contains(a.QhseXyViolationId))
            .ToListAsync();
        foreach(var violation in violations)
        {
            if (!string.IsNullOrEmpty(violation.Pic))
            {
                List<string> namesList = violation.Pic.Split(", ")
                                    .Select(name => name.Trim())  // 去除每个名字前后的空格
                                    .ToList();
                foreach (var name in namesList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }
            
        }
        _context.RemoveRange(violations);
        //清缓存
        await _cacheService.ClearViolationCacheAsync();
        await _cacheService.ClearVTotalCacheAsync();
        int ret = await _context.SaveChangesAsync();
        return ret;
    }


    public async Task<PageResultModel<QHSEXyViolationModel>> GetComPageAsync(ComViolationPageInputModel input)
    {

        var result = new PageResultModel<QHSEXyViolationModel>();
        if (!string.IsNullOrEmpty(input.id))
        {
            var violation = await _context.Set<QHSESysXyViolation>().AsNoTracking().Where(u => u.QhseXyViolationId == input.id).Join(_context.Set<QHSESysOrganization>(),
                            violation => violation.OrganizationId,
                            organization => organization.OrganizationId,
                            (violation, organization) => new { Violation = violation, Organization = organization })
                            .Join(_context.Set<QHSESysOrganization>(),
                                violationUserOrganization => violationUserOrganization.Violation.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (violationUserOrganization, organization) => new {
                                    ViolationUserOrganization = violationUserOrganization.Violation,
                                    OrganizationName = violationUserOrganization.Organization.NamePath,
                                    CheckOrganizationName = organization.NamePath,
                                    CheckOrganizationId = organization.OrganizationId
                                })
                            .Select(joinResult => new QHSEXyViolationModel
                            {
                                QhseXyViolationId = joinResult.ViolationUserOrganization.QhseXyViolationId,
                                UserName = joinResult.ViolationUserOrganization.UserName,
                                CheckDate = (DateTime)joinResult.ViolationUserOrganization.CheckDate,
                                OrganizationName = joinResult.OrganizationName,
                                CheckOrganizationName = joinResult.CheckOrganizationName,
                                WorkItem = joinResult.ViolationUserOrganization.WorkItem,
                                ViolationUserName = joinResult.ViolationUserOrganization.ViolationUserName,
                                Score = (float)joinResult.ViolationUserOrganization.Score,
                                Fine = (float)joinResult.ViolationUserOrganization.Fine,
                                ViolationType = joinResult.ViolationUserOrganization.ViolationType,
                                Description = joinResult.ViolationUserOrganization.Description,
                                TimesViolation = joinResult.ViolationUserOrganization.TimesViolation,
                                EnterTime = joinResult.ViolationUserOrganization.EnterTime,
                                SecondOrganizationId = joinResult.ViolationUserOrganization.SecondOrganizationId
                            })
                            .ToListAsync();
            var idResult = new PageResultModel<QHSEXyViolationModel>
            {
                TotalRows = 1,
                Rows = violation,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            return idResult;
        }
        if (utilService.isAnyNotEmpty(input))
        {
            return result;
        }
            IQueryable<QHSESysXyViolation> query = _context.Set<QHSESysXyViolation>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            if (input.CheckTypes.Count != 0)
            {
                string checkTypes = string.Join("", input.CheckTypes);
                query = query.Where(u => checkTypes.Contains(u.Check_Type));
            }
            if (!string.IsNullOrEmpty(input.OrganizationName))
            {
                var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
                if (org != null)
                {
                    var escapedNamePath = EscapeLikePattern(org.NamePath);
                    query = query
                        .Join(_context.Set<QHSESysOrganization>(),
                            violation => violation.OrganizationId,
                            organization => organization.OrganizationId,
                            (violation, organization) => new { Violation = violation, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                        .Select(joinResult => joinResult.Violation);
                }
            }
            if (!string.IsNullOrEmpty(input.ViolationCategory))
            {
                var cate = await _violationCategoryService.GetChildren(input.ViolationCategory);
                if (cate.Count != 0)
                {
                    query = query.Where(u => cate.Contains(u.ViolationCategory));
                }
                else
                {
                    query = query.Where(u => EF.Functions.Like(u.ViolationCategory, $"%{input.ViolationCategory}%"));
                }

            }
            if (input.TimesViolation.Any())
            {
                query = query.Where(u => (input.TimesViolation.Contains((int)u.TimesViolation)));
            }
            if (!string.IsNullOrEmpty(input.UserName))
            {
                query = query.Where(u => EF.Functions.Like(u.UserName, $"%{input.UserName}%"));
            }
            if (input.EnterSys.Count!=0)
            {
                query = query.Where(u => input.EnterSys.Contains(u.EnterSys));
            }
            if (!string.IsNullOrEmpty(input.UserId))
            {
                query = query.Where(u => u.ViolationUserId == input.UserId);
            }
            if (input.WorkRisk.Count != 0)
            {
                query = query.Where(u => input.WorkRisk.Contains(u.WorkRisk));
            }
            if (input.WorkType.Count != 0)
            {
                query = query.Where(u => input.WorkType.Contains(u.WorkSpecialty + "-" + u.WorkType));
            }
            if (input.Location.Count != 0)
            {
                query = query.Where(u => input.Location.Contains(u.WorkSpecialty+"-"+u.Location));
            }
            if (input.Position.Count != 0)
            {
                query = query.Where(u => input.Position.Contains(u.WorkSpecialty + "-" + u.Position));
            }
            if (input.WorkSpecialty.Count != 0)
            {
                string specialties = string.Join(",", input.WorkSpecialty);
                query = query.Where(u => specialties.Contains(u.WorkSpecialty));
            }
            if (input.ViolationType.Count != 0)
            {
                query = query.Where(u => input.ViolationType.Contains(u.ViolationType));
            }
            if (input.EmployeeType.Count != 0)
            {
                query = query.Where(u => input.EmployeeType.Contains(u.EmployeeType));
            }
            if (input.Age.Count != 0)
            {
                query = query.Where(u => input.Age.Contains(u.Age));
            }
            if (input.WorkYears.Count != 0)
            {
                query = query.Where(u => input.WorkYears.Contains(u.WorkYears));
            }
            if (input.Score != null && !string.IsNullOrEmpty(input.ScoreSymbol))
            {
                switch (input.ScoreSymbol)
                {
                    case ">":
                        // 执行大于的操作
                        query = query.Where(u => u.Score > input.Score);
                        break;
                    case "<":
                        // 执行小于的操作
                        query = query.Where(u => u.Score < input.Score);
                        break;
                    case ">=":
                        // 执行大于等于的操作
                        query = query.Where(u => u.Score >= input.Score);
                        break;
                    case "<=":
                        // 执行小于等于的操作
                        query = query.Where(u => u.Score <= input.Score);
                        break;
                    case "=":
                        // 执行等于的操作
                        query = query.Where(u => u.Score == input.Score);
                        break;
                }
            }
            if (input.Fine != null && !string.IsNullOrEmpty(input.FineSymbol))
            {
                switch (input.FineSymbol)
                {
                    case ">":
                        // 执行大于的操作
                        query = query.Where(u => u.Fine > input.Fine);
                        break;
                    case "<":
                        // 执行小于的操作
                        query = query.Where(u => u.Fine < input.Fine);
                        break;
                    case ">=":
                        // 执行大于等于的操作
                        query = query.Where(u => u.Fine >= input.Fine);
                        break;
                    case "<=":
                        // 执行小于等于的操作
                        query = query.Where(u => u.Fine <= input.Fine);
                        break;
                    case "=":
                        // 执行等于的操作
                        query = query.Where(u => u.Fine == input.Fine);
                        break;
                }
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationName))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            if (!string.IsNullOrEmpty(input.InspectionMethod))
            {
                query = query.Where(u => input.InspectionMethod.Equals(u.InspectionMethod));
            }
            var totalRows = await query.LongCountAsync();
            var violations = await query
                            .Join(_context.Set<QHSESysOrganization>(),
                            violation => violation.OrganizationId,
                            organization => organization.OrganizationId,
                            (violation, organization) => new { Violation = violation, Organization = organization })
                            .Join(_context.Set<QHSESysOrganization>(),
                                violationUserOrganization => violationUserOrganization.Violation.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (violationUserOrganization, organization) => new {
                                    ViolationUserOrganization = violationUserOrganization.Violation,
                                    OrganizationName = violationUserOrganization.Organization.NamePath,
                                    CheckOrganizationName = organization.NamePath,
                                    CheckOrganizationId = organization.OrganizationId
                                })
                            .Select(joinResult => new QHSEXyViolationModel
                            {
                                QhseXyViolationId = joinResult.ViolationUserOrganization.QhseXyViolationId,
                                UserName = joinResult.ViolationUserOrganization.UserName,
                                CheckDate = (DateTime)joinResult.ViolationUserOrganization.CheckDate,
                                OrganizationName = joinResult.OrganizationName,
                                CheckOrganizationName = joinResult.CheckOrganizationName,
                                WorkItem = joinResult.ViolationUserOrganization.WorkItem,
                                ViolationUserName = joinResult.ViolationUserOrganization.ViolationUserName,
                                Score = (float)joinResult.ViolationUserOrganization.Score,
                                Fine = (float)joinResult.ViolationUserOrganization.Fine,
                                ViolationType = joinResult.ViolationUserOrganization.ViolationType,
                                Description = joinResult.ViolationUserOrganization.Description,
                                TimesViolation = joinResult.ViolationUserOrganization.TimesViolation,
                                EnterTime = joinResult.ViolationUserOrganization.EnterTime,
                                SecondOrganizationId = joinResult.ViolationUserOrganization.SecondOrganizationId
                            })
                            .OrderByDescending(u => u.CheckDate)
                            .Page(input.PageNo, input.PageSize)
                            .ToListAsync();


            result = new PageResultModel<QHSEXyViolationModel>
            {
                TotalRows = (int)totalRows,
                Rows = violations,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
        result.SetPage(input);
        result.CountTotalPage();
        return result;
    }
    private async Task<IQueryable<QHSEXyViolationModel>> GetBaseQuery(ComViolationPageInputModel input)
    {
        IQueryable<QHSESysXyViolation> query = _context.Set<QHSESysXyViolation>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
        if (input.CheckTypes.Count != 0)
        {
            string checkTypes = string.Join("", input.CheckTypes);
            query = query.Where(u => checkTypes.Contains(u.Check_Type));
        }
        if (!string.IsNullOrEmpty(input.OrganizationName))
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            }
        }
        if (!string.IsNullOrEmpty(input.ViolationCategory))
        {
            var cate = await _violationCategoryService.GetChildren(input.ViolationCategory);
            if (cate.Count != 0)
            {
                query = query.Where(u => cate.Contains(u.ViolationCategory));
            }
            else
            {
                query = query.Where(u => EF.Functions.Like(u.ViolationCategory, $"%{input.ViolationCategory}%"));
            }

        }
        if (input.TimesViolation.Any())
        {
            query = query.Where(u => (input.TimesViolation.Contains((int)u.TimesViolation)));
        }
        if (!string.IsNullOrEmpty(input.UserName))
        {
            query = query.Where(u => EF.Functions.Like(u.UserName, $"%{input.UserName}%"));
        }
        if (input.EnterSys.Count != 0)
        {
            query = query.Where(u => input.EnterSys.Contains(u.EnterSys));
        }
        if (!string.IsNullOrEmpty(input.UserId))
        {
            query = query.Where(u => u.ViolationUserId == input.UserId);
        }
        if (input.WorkRisk.Count != 0)
        {
            query = query.Where(u => input.WorkRisk.Contains(u.WorkRisk));
        }
        if (input.WorkType.Count != 0)
        {
            query = query.Where(u => input.WorkType.Contains(u.WorkSpecialty + "-" + u.WorkType));
        }
        if (input.Location.Count != 0)
        {
            query = query.Where(u => input.Location.Contains(u.WorkSpecialty + "-" + u.Location));
        }
        if (input.Position.Count != 0)
        {
            query = query.Where(u => input.Position.Contains(u.WorkSpecialty + "-" + u.Position));
        }
        if (input.WorkSpecialty.Count != 0)
        {
            string specialties = string.Join(",", input.WorkSpecialty);
            query = query.Where(u => specialties.Contains(u.WorkSpecialty));
        }
        if (input.ViolationType.Count != 0)
        {
            query = query.Where(u => input.ViolationType.Contains(u.ViolationType));
        }
        if (input.EmployeeType.Count != 0)
        {
            query = query.Where(u => input.EmployeeType.Contains(u.EmployeeType));
        }
        if (input.Age.Count != 0)
        {
            query = query.Where(u => input.Age.Contains(u.Age));
        }
        if (input.WorkYears.Count != 0)
        {
            query = query.Where(u => input.WorkYears.Contains(u.WorkYears));
        }
        if (input.Score != null && !string.IsNullOrEmpty(input.ScoreSymbol))
        {
            switch (input.ScoreSymbol)
            {
                case ">":
                    // 执行大于的操作
                    query = query.Where(u => u.Score > input.Score);
                    break;
                case "<":
                    // 执行小于的操作
                    query = query.Where(u => u.Score < input.Score);
                    break;
                case ">=":
                    // 执行大于等于的操作
                    query = query.Where(u => u.Score >= input.Score);
                    break;
                case "<=":
                    // 执行小于等于的操作
                    query = query.Where(u => u.Score <= input.Score);
                    break;
                case "=":
                    // 执行等于的操作
                    query = query.Where(u => u.Score == input.Score);
                    break;
            }
        }
        if (input.Fine != null && !string.IsNullOrEmpty(input.FineSymbol))
        {
            switch (input.FineSymbol)
            {
                case ">":
                    // 执行大于的操作
                    query = query.Where(u => u.Fine > input.Fine);
                    break;
                case "<":
                    // 执行小于的操作
                    query = query.Where(u => u.Fine < input.Fine);
                    break;
                case ">=":
                    // 执行大于等于的操作
                    query = query.Where(u => u.Fine >= input.Fine);
                    break;
                case "<=":
                    // 执行小于等于的操作
                    query = query.Where(u => u.Fine <= input.Fine);
                    break;
                case "=":
                    // 执行等于的操作
                    query = query.Where(u => u.Fine == input.Fine);
                    break;
            }
        }
        if (!string.IsNullOrEmpty(input.CheckOrganizationName))
        {
            var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
            if (corg != null)
            {
                var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                query = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Violation);
            }
        }
        if (!string.IsNullOrEmpty(input.InspectionMethod))
        {
            query = query.Where(u => input.InspectionMethod.Equals(u.InspectionMethod));
        }
        var totalRows = await query.LongCountAsync();
        var newquery = query
                        .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                        .Join(_context.Set<QHSESysOrganization>(),
                            violationUserOrganization => violationUserOrganization.Violation.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (violationUserOrganization, organization) => new {
                                ViolationUserOrganization = violationUserOrganization.Violation,
                                OrganizationName = violationUserOrganization.Organization.NamePath,
                                CheckOrganizationName = organization.NamePath,
                                CheckOrganizationId = organization.OrganizationId
                            })
                        .Select(joinResult => new QHSEXyViolationModel
                        {
                            QhseXyViolationId = joinResult.ViolationUserOrganization.QhseXyViolationId,
                            Check_Type = joinResult.ViolationUserOrganization.Check_Type,
                            UserName = joinResult.ViolationUserOrganization.UserName,
                            CheckDate = (DateTime)joinResult.ViolationUserOrganization.CheckDate,
                            OrganizationName = joinResult.OrganizationName,
                            CheckOrganizationName = joinResult.CheckOrganizationName,
                            WorkItem = joinResult.ViolationUserOrganization.WorkItem,
                            WorkSpecialty = joinResult.ViolationUserOrganization.WorkSpecialty,
                            WorkType = joinResult.ViolationUserOrganization.WorkType,
                            WorkContent = joinResult.ViolationUserOrganization.WorkContent,
                            WorkRisk = joinResult.ViolationUserOrganization.WorkRisk,
                            Location = joinResult.ViolationUserOrganization.Location,
                            ViolationUserName = joinResult.ViolationUserOrganization.ViolationUserName,
                            ViolationUserDepart = joinResult.ViolationUserOrganization.ViolationUserDepart,
                            Position = joinResult.ViolationUserOrganization.Position,
                            Erp = joinResult.ViolationUserOrganization.Erp,
                            Age = joinResult.ViolationUserOrganization.Age,
                            WorkYears = joinResult.ViolationUserOrganization.WorkYears,
                            EmployeeType = joinResult.ViolationUserOrganization.EmployeeType,
                            Score = (float)joinResult.ViolationUserOrganization.Score,
                            Fine = (float)joinResult.ViolationUserOrganization.Fine,
                            ViolationTypeCategory = joinResult.ViolationUserOrganization.ViolationTypeCategory,
                            ViolationType = joinResult.ViolationUserOrganization.ViolationType,
                            ViolationClause = joinResult.ViolationUserOrganization.ViolationClause,
                            Description = joinResult.ViolationUserOrganization.Description,
                            ViolationCategory = joinResult.ViolationUserOrganization.ViolationCategory,
                            HseElement = joinResult.ViolationUserOrganization.HseElement,
                            ObserveComm = joinResult.ViolationUserOrganization.ObserveComm,
                            CauseAnalysis = joinResult.ViolationUserOrganization.CauseAnalysis,
                            DirectDepartment = joinResult.ViolationUserOrganization.DirectDepartment,
                            PotentialConsequence = joinResult.ViolationUserOrganization.PotentialConsequence,
                            Pic = joinResult.ViolationUserOrganization.Pic
                        })
                        .OrderByDescending(u => u.CheckDate).AsQueryable();
        return newquery;
    }

    private async Task<int> GetTotalPageAsync(IQueryable<QHSEXyViolationModel> baseQuery)
    {
        var totalRows = await baseQuery.CountAsync();
        if (totalRows < 1000)
        {
            return 1;
        }
        var pageSize = 1000;
        return totalRows / pageSize +1;
    }
    public async Task<byte[]> UploadExcel(ComViolationPageInputModel model)
    {
        List<QHSEXyViolationModel> allData = new List<QHSEXyViolationModel>();
        int totalPage = 0;

        // 获取总页数,一次取1000条数据
        model.PageSize = 1000;
        // 假设这里的 GetBaseQuery 是构建基本查询语句的方法
        var baseQuery =await GetBaseQuery(model);

        // 获取总页数
        totalPage = await GetTotalPageAsync(baseQuery);

        for (int pageNo = 1; pageNo <= totalPage; pageNo++)
        {
            // 设置当前页码
            model.PageNo = pageNo;

            // 构造分页的 SQL
            List<QHSEXyViolationModel> currentPageData = await baseQuery.Skip((pageNo - 1) * model.PageSize).Take(model.PageSize).ToListAsync();

            // 将当前页的数据添加到总数据集合
            allData.AddRange(currentPageData);
        }

        // 设置 ExcelPackage 的 LicenseContext
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        // 创建一个新的Excel包
        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

            // 写入表头并设置字体加粗
            for (int col = 0; col <= 28; col++)
            {
                ExcelRange headerCell = worksheet.Cells[1, col+1];
                headerCell.Value = GetHeaderTitle(col);
                headerCell.Style.Font.Bold = true;
            }

            // 写入数据
            for (int row = 0; row < allData.Count; row++)
            {
                QHSEXyViolationModel data = allData[row];
                worksheet.Cells[row + 2, 1].Value = data.Check_Type;
                worksheet.Cells[row + 2, 2].Value = data.CheckOrganizationName;
                worksheet.Cells[row + 2, 3].Value = data.UserName;
                worksheet.Cells[row + 2, 4].Value = data.CheckDate.ToString();
                worksheet.Cells[row + 2, 5].Value = data.OrganizationName;
                worksheet.Cells[row + 2, 6].Value = data.WorkItem;
                worksheet.Cells[row + 2, 7].Value = data.WorkSpecialty;
                worksheet.Cells[row + 2, 8].Value = data.WorkType;
                worksheet.Cells[row + 2, 9].Value = data.WorkContent;
                worksheet.Cells[row + 2, 10].Value = data.WorkRisk;
                worksheet.Cells[row + 2, 11].Value = data.Location;
                worksheet.Cells[row + 2, 12].Value = data.ViolationUserName;
                worksheet.Cells[row + 2, 13].Value = data.ViolationUserDepart;
                worksheet.Cells[row + 2, 14].Value = data.Erp;
                worksheet.Cells[row + 2, 15].Value = data.Position;
                worksheet.Cells[row + 2, 16].Value = data.Age;
                worksheet.Cells[row + 2, 17].Value = data.WorkYears;
                worksheet.Cells[row + 2, 18].Value = data.EmployeeType;
                worksheet.Cells[row + 2, 19].Value = data.Score;
                worksheet.Cells[row + 2, 20].Value = data.Fine;
                worksheet.Cells[row + 2, 21].Value = data.ViolationTypeCategory;
                worksheet.Cells[row + 2, 22].Value = data.ViolationCategory;
                worksheet.Cells[row + 2, 23].Value = data.ViolationClause;
                worksheet.Cells[row + 2, 24].Value = data.Description;
                worksheet.Cells[row + 2, 25].Value = data.ViolationType;
                worksheet.Cells[row + 2, 26].Value = data.HseElement;
                worksheet.Cells[row + 2, 27].Value = data.ObserveComm;
                worksheet.Cells[row + 2, 28].Value = data.CauseAnalysis;
                worksheet.Cells[row + 2, 29].Value = data.DirectDepartment;
                worksheet.Cells[row + 2, 30].Value = data.PotentialConsequence;
            }

            // 设置列宽
            for (int i = 1; i <= 29; i++)
            {
                worksheet.Column(i).AutoFit();
            }

            // 生成Excel文件的字节数组
            byte[] fileBytes = package.GetAsByteArray();
            return fileBytes;
        }
    }

    // Custom method to get header titles based on column index
    private string GetHeaderTitle(int columnIndex)
    {
        switch (columnIndex)
        {
            case 0: return "检查类型";
            case 1: return "检查单位";
            case 2: return "检查人员姓名";
            case 3: return "检查日期";
            case 4: return "被检单位名称";
            case 5: return "作业项目";
            case 6: return "作业专业";
            case 7: return "关键作业类型";
            case 8: return "关键作业内容";
            case 9: return "作业风险等级";
            case 10: return "位置区域";
            case 11: return "违章人员姓名";
            case 12: return "违章人员单位名称";
            case 13: return "身份证号（工号）";
            case 14: return "违章岗位";
            case 15: return "年龄";
            case 16: return "工龄";
            case 17: return "用工性质";
            case 18: return "违章扣分";
            case 19: return "罚款";
            case 20: return "违章性质分类";
            case 21: return "违章类别";
            case 22: return "违章条款";
            case 23: return "违章描述";
            case 24: return "违章性质";
            case 25: return "对应要素体系";
            case 26: return "对应安全沟通类型";
            case 27: return "原因分析";
            case 28: return "归属直线部门";
            case 29: return "可能后果";
            default: return string.Empty;
        }
    }
    public async Task<List<ViolationAnalysisResult>> WorkSpecialtyAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetWorkSpecialtyAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();
        if (org != null)
        {
            var escapedNamePath = EscapeLikePattern(org.NamePath);

            query = query
                .AsNoTracking()
                .Where(u =>
                    input.CheckType.Contains(u.Check_Type)&&
                    u.CheckDate >= input.TimeOn &&
                    u.CheckDate <= input.TimeEnd &&
                    _context.Set<QHSESysOrganization>()
                        .Any(organization =>
                            organization.OrganizationId == u.OrganizationId &&
                            EF.Functions.Like(organization.NamePath, $"{escapedNamePath}%", "/")));
        }
        var newQuery = query.Select(joinResult => joinResult.WorkSpecialty);
        if (input.CheckOrganizationId != null)
        {
            var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult =>joinResult.Violation.WorkSpecialty);
        }
        

        List<QHSEKeyWorkSpecialtyModel> workSpecialty = await _keyWorkSpecialtyService.GetAsync();
        if (workSpecialty != null)
        {
            var specialtyQueries = workSpecialty.Select(async specialty =>
            {
                var value = await newQuery.Where(u => specialty.Specialty.Contains(u)).CountAsync();
                return new ViolationAnalysisResult
                {
                    Name = specialty.Specialty,
                    Value = value
                };
            });

            // 并行执行所有专业查询
            var specialtyResults = await Task.WhenAll(specialtyQueries);

            // 将结果添加到结果列表
            result.AddRange(specialtyResults);
        }
        await _cacheService.SetWorkSpecialtyAnalysisAsync(input,result);
        return result;
    }

    public async Task<List<ViolationAnalysisResult>> ViolationSpecialtyAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationTrendAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);

        if (!string.IsNullOrEmpty(input.TotalDate))
        {
            switch (input.TotalDate)
            {
                case "周":
                    await AddWeeklyDataAsync(input, query, result);
                    break;
                case "月":
                    await AddMonthlyDataAsync(input, query, result);
                    break;
                case "季度":
                    await AddQuarterlyDataAsync(input, query, result);
                    break;
                default:
                    await AddYearlyDataAsync(input, query, result);
                    break;
            }
        }
        await _cacheService.SetSpecialityViolationTrendAnalysisAsync(input, result);
        return result;
    }

    private async Task<IQueryable<QHSESysXyViolation>> ApplyCommonFilters(IQueryable<QHSESysXyViolation> query, ViolationSpecialityInput input)
    {
        if (!string.IsNullOrEmpty(input.OrganizationName) &&
            !string.IsNullOrEmpty(input.WorkSpeciality) &&
            input.TimeOn != null &&
            input.TimeEnd != null &&
            input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.WorkSpecialty == input.WorkSpeciality);
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);

                query = query
                .Where(violation => _context.Set<QHSESysOrganization>()
                    .Any(organization => organization.OrganizationId == violation.OrganizationId &&
                                         EF.Functions.Like(organization.NamePath, $"{escapedNamePath}%", "/")));
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
        }
        return query;
    }

    //违章专业分析
    public async Task<List<ViolationAnalysisResult>> ViolationKeyWorkTypeAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationKeyWorkTypeAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        var keyWorkType = await _keyWorkTypeService.GetKeyWorktypesAsync(input.WorkSpeciality);
        if (keyWorkType == null)
        {
            return null;
        }
        var keyWorkCounts = await query.Where(u=>keyWorkType.Contains(u.WorkType))
            .GroupBy(u => u.WorkType).Select(group => new
            {
                WorkType = group.Key,
                Count = group.Count()
            })
            .ToListAsync();
        // 构建结果列表
        result = keyWorkType.Select(p => new ViolationAnalysisResult
        {
            Name = p,
            Value = keyWorkCounts.FirstOrDefault(pc => pc.WorkType == p)?.Count ?? 0
        }).ToList();
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationKeyWorkTypeAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationPositionAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationPositionAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query = await ApplyCommonFilters(query, input);

        // 获取特定专业和岗位
        var special = await _context.Set<QHSESysKeyWorkSpecialty>()
            .Where(u => u.Specialty == input.WorkSpeciality)
            .Select(u => u.SpecialtyCode)
            .FirstOrDefaultAsync();

        if (special == null)
        {
            return result; // 如果没有找到相应的专业，返回空结果
        }

        var positions = await _context.Set<QHSESysPosition>()
            .Where(u => u.SpecialtyCode == special)
            .Select(u=>u.Position)
            .ToListAsync();

        if (positions == null || positions.Count == 0)
        {
            return result; // 如果没有找到相应的岗位，返回空结果
        }
        // 进行批量查询
        var positionCounts = await query
            .Where(u => positions.Contains(u.Position))
            .GroupBy(u => u.Position)
            .Select(group => new
            {
                Position = group.Key,
                Count = group.Count()
            })
            .ToListAsync();

        // 构建结果列表
        result = positions.Select(p => new ViolationAnalysisResult
        {
            Name = p,
            Value = positionCounts.FirstOrDefault(pc => pc.Position == p)?.Count ?? 0
        }).ToList();

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationPositionAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationLocationAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationLocationAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        var location = await _locaitionService.GetLocationAsync(input.WorkSpeciality);
            if (location != null)
            {
                foreach (var item in location)
                {
                    var value = await query.Where(u => item.Location == u.Location || item.LocationCode == u.LocationCode).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = item.Location;
                    res.Value = value;
                    result.Add(res);
                }
            }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationLocationAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationViolationTypeAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationTypeAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        var violationTypes = await _violationTypeService.GetAsync();
            if (violationTypes != null)
            {
                foreach (var item in violationTypes)
                {
                    var value = await query.Where(u => item.ViolationType == u.ViolationType || item.ViolationTypeCode == u.ViolationTypeCode).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = item.ViolationType;
                    res.Value = value;
                    result.Add(res);
                }
            }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationTypeAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationViolationTypeCategoryAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationTypeCategoryAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        var violationTypeCategorys = await _dictionaryService.GetDictionaryAsync("1014");
            if (violationTypeCategorys != null)
            {
                foreach (var violationTypeCategory in violationTypeCategorys)
                {
                    if(violationTypeCategory.Code == "10140003")
                    {
                        violationTypeCategory.Item = "不区分+其他";
                    }
                    var value = await query.Where(u => violationTypeCategory.Item.Contains(u.ViolationTypeCategory)).CountAsync();
                if (violationTypeCategory.Code == "10140003")
                {
                    violationTypeCategory.Item = "其他";
                }
                ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = violationTypeCategory.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationTypeCategoryAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationViolationCauseAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationCauseAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        List<QHSEDictionaryModel> causeAnalysis = await _dictionaryService.GetDictionaryAsync("1007");
            if (causeAnalysis != null)
            {
                foreach (var cause in causeAnalysis)
                {
                    var value = await query.Where(u => cause.Item.Contains(u.CauseAnalysis) || cause.Code == u.ClauseAnalysisCode).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = cause.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationCauseAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationDirectDepartmentAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationDirectDepartmentAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        List<QHSEDictionaryModel> directDepartment = await _dictionaryService.GetDictionaryAsync("1008");
            if (directDepartment != null)
            {
                foreach (var department in directDepartment)
                {
                    var newitem = "";
                    if(department.Item.Contains("生产"))
                    {
                    newitem = "生产";
                    }
                    else if (department.Item.Contains("人力资源"))
                    {
                    newitem = "人力资源（培训）";
                    }
                    var value = await query.Where(u => department.Item.Contains(u.DirectDepartment) || newitem.Contains(u.DirectDepartment) || u.DepartmentCode == department.Code).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = department.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationDirectDepartmentAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationHSEElementAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationHSEElementAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        List<QHSEViolationHSEModel> hseElement = await _violationHSEService.GetAsync();
        var hses = hseElement.Select(u=>u.Item).ToList();
            if (hseElement != null)
            {
            // 进行批量查询
            var hseCounts = await query
                .Where(u => hses.Contains(u.HseElement))
                .GroupBy(u => u.HseElement)
                .Select(group => new
                {
                    HseElement = group.Key,
                    Count = group.Count()
                })
                .ToListAsync();

            // 构建结果列表
            result = hses.Select(p => new ViolationAnalysisResult
            {
                Name = p,
                Value = hseCounts.FirstOrDefault(pc => pc.HseElement == p)?.Count ?? 0
            }).ToList();
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationHSEElementAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationWorkYearsAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationWorkYearsAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        List<QHSEDictionaryModel> workYears = await _dictionaryService.GetDictionaryAsync("1003");
            if (workYears != null)
            {
                foreach (var workYear in workYears)
                {
                var newitem = "";
                if(workYear.Item == "1年及以下")
                {
                    newitem = "1年以下";
                }
                else if (workYear.Item == "2-3年")
                {
                    newitem = "1-5年";
                }
                else if (workYear.Item == "5年以上")
                {
                    newitem = "10-15年+15年以上";
                }
                var value = await query.Where(u => workYear.Item.Contains(u.WorkYears) || newitem.Contains(u.WorkYears) || workYear.Code == u.WorkYearsCode).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = workYear.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationWorkYearsAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> ViolationAgeAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationAgeAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);

        List<QHSEDictionaryModel> ages = await _dictionaryService.GetDictionaryAsync("1002");
        if (ages != null)
        {
            foreach (var age in ages)
            {
                var value = await query.Where(u => EF.Functions.Like(u.Age, $"%{age.Item}%") || u.AgeCode == age.Code).CountAsync();
                ViolationAnalysisResult res = new ViolationAnalysisResult();
                res.Name = age.Item;
                res.Value = value;
                result.Add(res);
            }
        }

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationAgeAnalysisAsync(input, result);
        return result;
    }

    public async Task<List<ViolationAnalysisResult>> ViolatioEmployeeTypeAnalysisAsync(ViolationSpecialityInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationEmployeeTypeAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        query =await ApplyCommonFilters(query, input);
        List<QHSEDictionaryModel> employeeType = await _dictionaryService.GetDictionaryAsync("1004");
            if (employeeType != null)
            {
                foreach (var type in employeeType)
                {
                    var value = await query.Where(u => type.Item.Contains(u.EmployeeType.Substring(0, 3)) || type.Code == u.EmployeeTypeCode).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = type.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationEmployeeTypeAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<ViolationAnalysisResult>> HseElementAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetHseElementAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        // Apply common filters
        var query =await GetFilteredQuery(input);
        List<QHSEViolationHSEModel> hseElement = await _violationHSEService.GetAsync();
        var hses = hseElement.Select(u => u.Item).ToList();
        if (hseElement != null)
            {
            // 进行批量查询
                var hseCounts = await query
                    .Where(u => hses.Contains(u.HseElement))
                    .GroupBy(u => u.HseElement)
                    .Select(group => new
                    {
                        HseElement = group.Key,
                        Count = group.Count()
                    })
                    .ToListAsync();

                // 构建结果列表
                result = hses.Select(p => new ViolationAnalysisResult
                {
                    Name = p,
                    Value = hseCounts.FirstOrDefault(pc => pc.HseElement == p)?.Count ?? 0
                }).ToList();
        }
        await _cacheService.SetHseElementAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationCauseAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetViolationCauseAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query =await GetFilteredQuery(input);
        List<QHSEDictionaryModel> causeAnalysis = await _dictionaryService.GetDictionaryAsync("1007");
            if (causeAnalysis != null)
            {
                foreach (var cause in causeAnalysis)
                {
                    var value = await query.Where(u => cause.Item.Contains(u.CauseAnalysis) ||cause.Code == u.ClauseAnalysisCode).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = cause.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        await _cacheService.SetViolationCauseAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }

    private async Task<IQueryable<QHSESysXyViolation>> GetFilteredQuery(ViolationAnalysisInput input)
    {
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);

                query = query
                .Where(violation => _context.Set<QHSESysOrganization>()
                    .Any(organization => organization.OrganizationId == violation.OrganizationId &&
                                         EF.Functions.Like(organization.NamePath, $"{escapedNamePath}%", "/")));
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
        }

        return query;
    }
    public async Task<List<ViolationAnalysisResult>> DirectDepartmentAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetDirectDepartmentAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query =await GetFilteredQuery(input);

        List<QHSEDictionaryModel> directDepartment = await _dictionaryService.GetDictionaryAsync("1008");
        if (directDepartment != null)
        {
            foreach (var department in directDepartment)
            {
                var newitem = "";
                if (department.Item.Contains("生产"))
                {
                    newitem = "生产";
                }
                else if (department.Item.Contains("人力资源"))
                {
                    newitem = "人力资源（培训）";
                }
                var value = await query.Where(u => department.Item.Contains(u.DirectDepartment) || newitem.Contains(u.DirectDepartment) ||u.DepartmentCode == department.Code).CountAsync();
                ViolationAnalysisResult res = new ViolationAnalysisResult();
                res.Name = department.Item;
                res.Value = value;
                result.Add(res);
            }
        }
        await _cacheService.SetDirectDepartmentAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }

    public async Task<List<ViolationAnalysisResult>> ViolationTypeCategoryAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetViolationTypeCategoryAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query =await GetFilteredQuery(input);
        var violationTypeCategorys =await  _dictionaryService.GetDictionaryAsync("1014");
            if (violationTypeCategorys != null)
            {
                foreach (var violationTypeCategory in violationTypeCategorys)
                {
                if (violationTypeCategory.Code == "10140003")
                {
                    violationTypeCategory.Item = "不区分+其他";
                }
                var value = await query.Where(u => violationTypeCategory.Item.Contains(u.ViolationTypeCategory)).CountAsync();
                if (violationTypeCategory.Code == "10140003")
                {
                    violationTypeCategory.Item = "其他";
                }
                ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = violationTypeCategory.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        await _cacheService.SetViolationTypeCategoryAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationTypeAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetViolationTypeAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query =await GetFilteredQuery(input);
        List<QHSEXyViolationTypeModel> violationType = await _violationTypeService.GetAsync();
            if (violationType != null)
            {
                foreach (var type in violationType)
            {
                    var value = await query.Where(u => type.ViolationType.Contains(u.ViolationType) || type.ViolationTypeCode == u.ViolationTypeCode).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = type.ViolationType;
                    res.Value = value;
                    result.Add(res);
                }
            }
        await _cacheService.SetViolationTypeAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> SecondaryUnitAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSecondaryUnitAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var iorg = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var escapedNamePath = EscapeLikePattern(iorg.NamePath);
            var joinedQuery = query
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"));
            List<QHSESysOrganization> orgs = await _organizationService.GetSon(input.OrganizationId);
            if (orgs != null)
            {
                foreach (var org in orgs)
                {
                    var value = joinedQuery.Where(joinResult => joinResult.Organization.NamePath.Contains(org.NamePath)).Count();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = org.OrganizationName;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        await _cacheService.SetSecondaryUnitAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationTrendAnalysisAsync(ViolationTrendInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetViolationTrendAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count!=0 && !string.IsNullOrEmpty(input.TotalDate))
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);

                query = query
                .Where(violation => _context.Set<QHSESysOrganization>()
                    .Any(organization => organization.OrganizationId == violation.OrganizationId &&
                                         EF.Functions.Like(organization.NamePath, $"{escapedNamePath}%", "/")));
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            switch (input.TotalDate)
            {
                case "周":
                    await AddWeeklyDataAsync(input, query, result);
                    break;
                case "月":
                    await AddMonthlyDataAsync(input, query, result);
                    break;
                case "季度":
                    await AddQuarterlyDataAsync(input, query, result);
                    break;
                default:
                    await AddYearlyDataAsync(input, query, result);
                    break;
            }
        }
        await _cacheService.SetViolationTrendAnalysisAsync(input, result);
        return result;
    }

    private async Task AddWeeklyDataAsync(ViolationAnalysisInput input, IQueryable<QHSESysXyViolation> query, List<ViolationAnalysisResult> result)
    {
        var yearOn = input.TimeOn?.Year ?? 0;
        var yearEnd = input.TimeEnd?.Year ?? 0;
        var weekOn = GetIso8601WeekOfYear(input.TimeOn.Value);
        var weekEnd = GetIso8601WeekOfYear(input.TimeEnd.Value);

        for (int year = yearOn; year <= yearEnd; year++)
        {
            int startWeek = (year == yearOn) ? weekOn : 1;
            int endWeek = (year == yearEnd) ? weekEnd : GetIso8601WeeksInYear(year);

            for (int week = startWeek; week <= endWeek; week++)
            {
                var startDate = FirstDateOfWeekISO8601(year, week);
                var endDate = startDate.AddDays(7);

                var value = await query
                    .Where(u => u.CheckDate.Year == year && u.CheckDate >= startDate && u.CheckDate <= endDate)
                    .CountAsync();

                ViolationAnalysisResult res = new ViolationAnalysisResult();
                res.Name = $"{year}年第{week}周";
                res.Value = value;
                result.Add(res);
            }
        }
    }

    private async Task AddMonthlyDataAsync(ViolationAnalysisInput input, IQueryable<QHSESysXyViolation> query, List<ViolationAnalysisResult> result)
    {
        var yearOn = input.TimeOn?.Year ?? 0;
        var yearEnd = input.TimeEnd?.Year ?? 0;
        var monthOn = input.TimeOn?.Month ?? 0;
        var monthEnd = input.TimeEnd?.Month ?? 0;

        for (int year = yearOn; year <= yearEnd; year++)
        {
            int startMonth = (year == yearOn) ? monthOn : 1;
            int endMonth = (year == yearEnd) ? monthEnd : 12;

            for (int month = startMonth; month <= endMonth; month++)
            {
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);

                var value = await query
                    .Where(u => u.CheckDate.Year == year && u.CheckDate.Month == month)
                    .CountAsync();

                ViolationAnalysisResult res = new ViolationAnalysisResult();
                res.Name = $"{year}年{month}月";
                res.Value = value;
                result.Add(res);
            }
        }
    }

    private async Task AddQuarterlyDataAsync(ViolationAnalysisInput input, IQueryable<QHSESysXyViolation> query, List<ViolationAnalysisResult> result)
    {
        var yearOn = input.TimeOn?.Year ?? 0;
        var yearEnd = input.TimeEnd?.Year ?? 0;
        var quarterOn = (input.TimeOn?.Month - 1) / 3 + 1;
        var quarterEnd = (input.TimeEnd?.Month - 1) / 3 + 1;

        for (int year = yearOn; year <= yearEnd; year++)
        {
            int startQuarter = (int)((year == yearOn) ? quarterOn : 1);
            int endQuarter = (int)((year == yearEnd) ? quarterEnd : 4);

            for (int quarter = startQuarter; quarter <= endQuarter; quarter++)
            {
                var startDate = new DateTime(year, (quarter - 1) * 3 + 1, 1);
                var endDate = startDate.AddMonths(3).AddDays(-1);

                var value = await query
                    .Where(u => u.CheckDate.Year == year && u.CheckDate >= startDate && u.CheckDate <= endDate)
                    .CountAsync();

                ViolationAnalysisResult res = new ViolationAnalysisResult();
                res.Name = $"{year}年第{quarter}季度";
                res.Value = value;
                result.Add(res);
            }
        }
    }

    private async Task AddYearlyDataAsync(ViolationAnalysisInput input, IQueryable<QHSESysXyViolation> query, List<ViolationAnalysisResult> result)
    {
        var yearOn = input.TimeOn?.Year ?? 0;
        var yearEnd = input.TimeEnd?.Year ?? 0;

        for (int year = yearOn; year <= yearEnd; year++)
        {
            var startDate = new DateTime(year, 1, 1);
            var endDate = new DateTime(year, 12, 31);

            var value = await query
                .Where(u => u.CheckDate.Year == year)
                .CountAsync();

            ViolationAnalysisResult res = new ViolationAnalysisResult();
            res.Name = $"{year}年";
            res.Value = value;
            result.Add(res);
        }
    }
    private int GetIso8601WeeksInYear(int year)
    {
        DateTime date = new DateTime(year, 12, 31);
        int weeksInYear = GetIso8601WeekOfYear(date);
        return weeksInYear;
    }

    private int GetIso8601WeekOfYear(DateTime date)
    {
        // Determine the week of the year for the given date
        int weekOfYear = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(date, CalendarWeekRule.FirstDay, DayOfWeek.Monday);

        // Check if the week is in the previous year
        if (weekOfYear == 52 && date.Month == 1)
        {
            weekOfYear = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(date.AddDays(-7), CalendarWeekRule.FirstDay, DayOfWeek.Monday);
        }

        return weekOfYear;
    }
    private DateTime FirstDateOfWeekISO8601(int year, int weekOfYear)
    {
        DateTime jan1 = new DateTime(year, 1, 1);
        int daysToFirstDay = (int)DayOfWeek.Monday - (int)jan1.DayOfWeek;

        if (daysToFirstDay > 0)
        {
            daysToFirstDay -= 7;
        }

        DateTime firstMonday = jan1.AddDays(daysToFirstDay);

        if (weekOfYear > 1)
        {
            firstMonday = firstMonday.AddDays((weekOfYear - 1) * 7);
        }

        return firstMonday;
    }
    public async Task<List<ViolationAnalysisResult>> WorkYearAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetWorkYearAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query =await GetFilteredQuery(input);
        List<QHSEDictionaryModel> workYears = await _dictionaryService.GetDictionaryAsync("1003");
            if (workYears != null)
            {
                foreach (var workYear in workYears)
                {
                    var newitem = "";
                    if (workYear.Item == "1年及以下")
                    {
                        newitem = "1年以下";
                    }
                    else if (workYear.Item == "2-3年")
                    {
                        newitem = "1-5年";
                    }
                    else if(workYear.Item == "5年以上")
                    {
                        newitem = "10-15年+15年以上";
                    }
                    var value = await query.Where(u => u.Age != null && (workYear.Item.Contains(u.WorkYears) || newitem.Contains(u.WorkYears) || workYear.Code == u.WorkYearsCode)).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = workYear.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        await _cacheService.SetWorkYearAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> AgeAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetAgeAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query =await GetFilteredQuery(input);
        List<QHSEDictionaryModel> ages = await _dictionaryService.GetDictionaryAsync("1002");
            if (ages != null)
            {
                foreach (var age in ages)
                {
                    var filteredQuery = query.Where(u => u.Age != null && (age.Item.Contains(u.Age) || age.Code == u.AgeCode));
                    var value = await filteredQuery.CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = age.Item;
                    res.Value = value;
                    result.Add(res);
                }
        }
        await _cacheService.SetAgeAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> EmployeeTypeAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetEmployeeTypeAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query =await GetFilteredQuery(input);
        List<QHSEDictionaryModel> employeeType = await _dictionaryService.GetDictionaryAsync("1004");
            if (employeeType != null)
            {
                foreach (var type in employeeType)
                {
                    var value = await query.Where(u => type.Item.Contains(u.EmployeeType.Substring(0, 3)) || type.Code == u.EmployeeTypeCode).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = type.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        await _cacheService.SetEmployeeTypeAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationCategoryAnalysisAsync(ViolationCategoryInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetViolationCategoryAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query =await GetFilteredQuery(input);
        var violationCategory = new List<string>();
            if (input.ViolationCategory == "违章类别")
            {
                violationCategory = await _context.Set<QHSESysViolationCategory>().Where(u => u.ParentId == "0").Select(u => u.Item).ToListAsync();
            }
            else
            {
                violationCategory = await _violationCategoryService.GetSon(input.ViolationCategory);
            }
            if (violationCategory != null)
            {
                foreach (var category in violationCategory)
                {
                    var children = await _violationCategoryService.GetChildren(category);
                    var value = await query.Where(u => children.Contains(u.ViolationCategory)).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = category;
                    res.Value = value;
                    result.Add(res);
                }
            }
        await _cacheService.SetViolationCategoryAnalysisAsync(input, result.OrderByDescending(result => result.Value).ToList());
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationSpecialtyCategoryAnalysisAsync(ViolationSpecialtyAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = await _cacheService.GetSpecialityViolationCategoryAnalysisAsync(input);
        if (result != null)
        {
            return result;
        }
        result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        // Apply common filters
        if (!string.IsNullOrEmpty(input.OrganizationName) &&
            input.TimeOn != null &&
            input.TimeEnd != null &&
            input.CheckType.Count != 0)
        {
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty));

            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            }

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            violation => violation.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (violation, organization) => new { Violation = violation, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Violation);
                }
            }
        }

        List<string> violationCategories = new List<string>();
        if (input.ViolationCategory == "违章类别")
        {
            violationCategories = await _context.Set<QHSESysViolationCategory>()
                                                .Where(u => u.ParentId == "0")
                                                .Select(u => u.Item)
                                                .ToListAsync();
        }
        else
        {
            violationCategories = await _violationCategoryService.GetSon(input.ViolationCategory);
        }

        if (violationCategories != null)
        {
            var violationAnalysisTasks = violationCategories.Select(async category =>
            {
                var children = await _violationCategoryService.GetChildren(category);
                var value = await query.Where(u => children.Contains(u.ViolationCategory)).CountAsync();
                return new ViolationAnalysisResult
                {
                    Name = category,
                    Value = value
                };
            });

            var analysisResults = await Task.WhenAll(violationAnalysisTasks);
            result = analysisResults.ToList();
        }

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityViolationCategoryAnalysisAsync(input, result);
        return result;
    }

    public async Task<ViolationUser> GetUserAsync(IdInputModel idInputModel)
    {
        var violation =await  _context.Set<QHSESysXyViolation>().OrderByDescending(u=>u.CheckDate).FirstOrDefaultAsync(u => u.ViolationUserId == idInputModel.Id);
        if(violation== null)
        {
            return new ViolationUser();
        }
        ViolationUser user = new ViolationUser
        {
            Age = violation.Age,
            WorkYears = violation.WorkYears,
            EmployeeType = violation.EmployeeType,
            ViolationUserDepart = violation.ViolationUserDepart,
            Position = violation.Position
        };
        return user;
    }
    public async Task<bool> AreFilesNotEqual(QHSEXyViolationModel model)
    {
        if (model.Files.Count > 1)
        {
            int num = model.Files.Count;
            byte[][] byteArray = new byte[num][];
            for(int i = 0; i < num;i++)
            {
                byteArray[i] = Convert.FromBase64String(model.Files[i].Base64Content);
            }
            byte[][] hashArray= new byte[num][];
            using (var sha256 = SHA256.Create())
            {
                for (int i = 0; i < num; i++)
                {
                    hashArray[i] = sha256.ComputeHash(byteArray[i]);
                }
            }
            for (int i = 0; i < hashArray.Length - 1; i++)
            {
                for (int j = i + 1; j < hashArray.Length; j++)
                {
                    if (!StructuralComparisons.StructuralEqualityComparer.Equals(hashArray[i], hashArray[j]))
                        return false;
                }
            }
            return true;
        }
        return true;
    }

    public async Task<List<QHSEViolationAdministerRepeatResultModel>> ViolationAdministerRepeatAsync(QHSEViolationAdministerRepeatInputModel input)
    {
        List<QHSEViolationAdministerRepeatResultModel> resultModel = new List<QHSEViolationAdministerRepeatResultModel>();
        if (input.CheckTypes.Count == 0 && string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn == null && input.TimeEnd == null && string.IsNullOrEmpty(input.WorkSpeciality) && input.ViolationTypes.Count == 0 && input.Repeat == 0)
        {
            return resultModel;
        }
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();
        if (input.CheckTypes.Count != 0)
        {
            query = query.Where(u => input.CheckTypes.Contains(u.Check_Type));
        }

        if (!string.IsNullOrEmpty(input.OrganizationName))
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            }
        }
        if (input.TimeOn != null && input.TimeEnd != null)
        {
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
        }

        if (!string.IsNullOrEmpty(input.WorkSpeciality))
        {
            query = query.Where(u => EF.Functions.Like(u.WorkSpecialty, $"%{input.WorkSpeciality}%"));
        }

        if (input.ViolationTypes.Count != 0)
        {
            query = query.Where(u => input.ViolationTypes.Contains(u.ViolationType));
        }
        // 使用 GroupBy 和 Count 来统计每个 dangerCategory 的个数
        resultModel = await query
            .GroupBy(u => u.ViolationType)
            .Select(g => new QHSEViolationAdministerRepeatResultModel
            {
                ViolationType = g.Key,
                Repeat = g.Count()
            })
            .Where(u => u.Repeat > input.Repeat)
            .OrderBy(u=>u.Repeat)
            .ToListAsync();
        return resultModel;
    }

    public async Task<PageResultModel<ViolationTotalResultModel>> ViolationTotalQueryAsync(ViolationTotalInputModel input)
    {
        PageResultModel<ViolationTotalResultModel> resultModel = new PageResultModel<ViolationTotalResultModel>();
        List<ViolationTotalResultModel> totalResultModel = new List<ViolationTotalResultModel>();
        if (input.TimeOn == null || input.TimeEnd == null)
        {
            return resultModel;
        }
        if(await _cacheService.GetViolationTotalQueryAsync(input) != null)
        {
            return await _cacheService.GetViolationTotalQueryAsync(input);
        }
        var secondaryUnit =await _organizationService.GetSon("00");
        var vquery = _context.Set<QHSESysXyViolation>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd).AsQueryable();
        if (!string.IsNullOrEmpty(input.InspectionMethod))
        {
            vquery = vquery.Where(u => input.InspectionMethod.Equals(u.InspectionMethod));
        }
        if (secondaryUnit != null)
        {
            var totalModel = new ViolationTotalResultModel()
            {
                SecondaryUnit = "总计",
            };
            foreach (var unit in secondaryUnit)
            {
                var escapedNamePath = EscapeLikePattern(unit.NamePath);
                var violationCounts =
                            await (
                            from violation in vquery
                            join organization in _context.Set<QHSESysOrganization>() on violation.OrganizationId equals organization.OrganizationId
                            where EF.Functions.Like(organization.NamePath, $"{escapedNamePath}%", "/")
                            select violation.Check_Type
                        )
                        .GroupBy(u => u)
                        .Select(group => new
                        {
                            CheckType = group.Key,
                            ViolationCount = group.Count()
                        })
                        .ToDictionaryAsync(result => result.CheckType, result => result.ViolationCount);
                var residentSupervisorViolation = 0;
                var patrolSupervisionViolation = 0;
                var electronicSupervisionViolation = 0;
                var monthViolation = 0;
                var weekViolation = 0;
                var staffViolation = 0;
                var nationalAndLocalViolation = 0;
                var groupCompanyViolation = 0;
                var companyLeadersViolation = 0;
                var companySystemViolation = 0;
                var companySpecificViolation = 0;
                var secondaryLeadersViolation = 0;
                var secondarySystemViolation = 0;
                var secondarySpecificViolation = 0;
                var thirdSpecificViolation = 0;
                var thirdLeadersViolation = 0;

                // 一次遍历 dangerCounts 字典，根据 CheckType 累加到对应的变量
                foreach (var kv in violationCounts)
                {
                    switch (kv.Key)
                    {
                        case "监督检查-驻井监督检查":
                            residentSupervisorViolation += kv.Value;
                            break;
                        case "监督检查-巡回监督检查":
                            patrolSupervisionViolation += kv.Value;
                            break;
                        case "监督检查-旁站监督检查":
                            electronicSupervisionViolation += kv.Value;
                            break;
                        case "基层队站-月检查":
                            monthViolation += kv.Value;
                            break;
                        case "基层队站-周检查":
                            weekViolation += kv.Value;
                            break;
                        case "岗位员工检查":
                            staffViolation += kv.Value;
                            break;
                        case "国家及地方检查":
                            nationalAndLocalViolation += kv.Value;
                            break;
                        case "集团公司及中油技服检查":
                            groupCompanyViolation += kv.Value;
                            break;
                        case "公司检查-领导检查承包点督导":
                            companyLeadersViolation += kv.Value;
                            break;
                        case "公司检查-体系审核":
                            companySystemViolation += kv.Value;
                            break;
                        case "公司检查-专项检查":
                            companySpecificViolation += kv.Value;
                            break;
                        case "二级单位-领导检查承包点督导":
                            secondaryLeadersViolation += kv.Value;
                            break;
                        case "二级单位-体系审核":
                            secondarySystemViolation += kv.Value;
                            break;
                        case "二级单位-专项检查":
                            secondarySpecificViolation += kv.Value;
                            break;
                        case "三级单位-领导检查":
                            thirdSpecificViolation += kv.Value;
                            break;
                        case "三级单位-专项检查":
                            thirdLeadersViolation += kv.Value;
                            break;
                    }
                }

                var supervisorViolationTotal = residentSupervisorViolation + patrolSupervisionViolation + electronicSupervisionViolation;
                var selfInspectionViolationTotal = monthViolation + weekViolation + staffViolation;
                var superiorUnitViolationTotal = groupCompanyViolation + nationalAndLocalViolation;
                var companyViolationTotal = companyLeadersViolation + companySpecificViolation + companySystemViolation;
                var secondaryViolationTotal = secondaryLeadersViolation + secondarySpecificViolation + secondarySystemViolation;
                var thirdViolationTotal = thirdLeadersViolation + thirdSpecificViolation;
                var violationTotal = supervisorViolationTotal + selfInspectionViolationTotal + superiorUnitViolationTotal + companyViolationTotal + secondaryViolationTotal + thirdViolationTotal;
                ViolationTotalResultModel violationTotalResultModel = new ViolationTotalResultModel()
                {
                    NationalAndLocalViolation = nationalAndLocalViolation,
                    GroupCompanyViolation = groupCompanyViolation,
                    SuperiorUnitViolationTotal = superiorUnitViolationTotal,
                    CompanyLeadersViolation = companyLeadersViolation,
                    CompanySystemViolation = companySystemViolation,
                    CompanySpecificViolation = companySpecificViolation,
                    CompanyViolationTotal = companyViolationTotal,
                    SecondaryLeadersViolation = secondaryLeadersViolation,
                    SecondarySystemViolation = secondarySystemViolation,
                    SecondarySpecificViolation = secondarySpecificViolation,
                    SecondaryViolationTotal = secondaryViolationTotal,
                    ThirdSpecificViolation = thirdSpecificViolation,
                    ThirdLeadersViolation = thirdLeadersViolation,
                    ThirdViolationTotal = thirdViolationTotal,
                    ResidentSupervisorViolation = residentSupervisorViolation,
                    PatrolSupervisionViolation = patrolSupervisionViolation,
                    MonthViolation = monthViolation,
                    WeekViolation = weekViolation,
                    SecondaryUnit = unit.OrganizationName,
                    ElectronicSupervisionViolation = electronicSupervisionViolation,
                    StaffViolation = staffViolation,
                    SelfInspectionViolationTotal = selfInspectionViolationTotal,
                    SupervisorViolationTotal = supervisorViolationTotal,
                    ViolationTotal = violationTotal
                };
                totalModel.NationalAndLocalViolation += violationTotalResultModel.NationalAndLocalViolation;
                totalModel.GroupCompanyViolation += violationTotalResultModel.GroupCompanyViolation;
                totalModel.SuperiorUnitViolationTotal += violationTotalResultModel.SuperiorUnitViolationTotal;
                totalModel.CompanyLeadersViolation += violationTotalResultModel.CompanyLeadersViolation;
                totalModel.CompanySystemViolation += violationTotalResultModel.CompanySystemViolation;
                totalModel.CompanySpecificViolation += violationTotalResultModel.CompanySpecificViolation;
                totalModel.CompanyViolationTotal += violationTotalResultModel.CompanyViolationTotal;
                totalModel.SecondaryLeadersViolation += violationTotalResultModel.SecondaryLeadersViolation;
                totalModel.SecondarySystemViolation += violationTotalResultModel.SecondarySystemViolation;
                totalModel.SecondarySystemViolation += violationTotalResultModel.SecondarySystemViolation;
                totalModel.SecondarySpecificViolation += violationTotalResultModel.SecondarySpecificViolation;
                totalModel.SecondaryViolationTotal += violationTotalResultModel.SecondaryViolationTotal;
                totalModel.ThirdLeadersViolation += violationTotalResultModel.ThirdLeadersViolation;
                totalModel.ThirdSpecificViolation += violationTotalResultModel.ThirdSpecificViolation;
                totalModel.ThirdViolationTotal += violationTotalResultModel.ThirdViolationTotal;
                totalModel.ResidentSupervisorViolation += violationTotalResultModel.ResidentSupervisorViolation;
                totalModel.PatrolSupervisionViolation += violationTotalResultModel.PatrolSupervisionViolation;
                totalModel.MonthViolation += violationTotalResultModel.MonthViolation;
                totalModel.WeekViolation += violationTotalResultModel.WeekViolation;
                totalModel.StaffViolation += violationTotalResultModel.StaffViolation;
                totalModel.ElectronicSupervisionViolation += violationTotalResultModel.ElectronicSupervisionViolation;
                totalModel.SupervisionInspectionTotal += violationTotalResultModel.SupervisionInspectionTotal;
                totalModel.SelfInspectionTotal += violationTotalResultModel.SelfInspectionTotal;
                totalModel.SelfInspectionViolationTotal += violationTotalResultModel.SelfInspectionViolationTotal;
                totalModel.SupervisorViolationTotal += violationTotalResultModel.SupervisorViolationTotal;
                totalModel.Total += violationTotalResultModel.Total;
                totalModel.ViolationTotal += violationTotalResultModel.ViolationTotal;
                totalResultModel.Add(violationTotalResultModel);
            }
            totalResultModel =totalResultModel.ToList();
            totalResultModel.Add(totalModel);
            resultModel.TotalRows = secondaryUnit.Count();
            resultModel.PageNo = input.PageNo;
            resultModel.PageSize = input.PageSize;
            resultModel.Rows = totalResultModel;
            resultModel.TotalPage = resultModel.CountTotalPage();
        }
        await _cacheService.SetViolationTotalQueryAsync(input, resultModel);
        return resultModel;
    }
    public byte[] UploadExcel()
    {
        // 设置 ExcelPackage 的 LicenseContext
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        // 创建一个新的Excel包
        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

            // 写入表头并设置字体加粗
            for (int col = 1; col <= 28; col++)
            {
                ExcelRange headerCell = worksheet.Cells[1, col];
                headerCell.Value = GetImportHeaderTitle(col);
                headerCell.Style.Font.Bold = true;
            }

            // 设置列宽
            for (int i = 1; i <= 28; i++)
            {
                worksheet.Column(i).AutoFit();
            }

            // 生成Excel文件的字节数组
            byte[] fileBytes = package.GetAsByteArray();
            return fileBytes;
        }
    }
    private string GetImportHeaderTitle(int columnIndex)
    {
        // 返回固定标题
        switch (columnIndex)
        {
            case 1: return "检查类型";
            case 2: return "检查日期";
            case 3: return "作业项目";
            case 4: return "作业专业";
            case 5: return "关键作业类型";
            case 6: return "关键作业内容";
            case 7: return "作业风险等级";
            case 8: return "位置区域";
            case 9: return "违章人员姓名";
            case 10: return "违章人员手机号或邮箱";
            case 11: return "违章人员单位名称";
            default: return string.Empty;
        }
    }

    public async Task<PageResultModel<QHSEXyViolationModel>> GetComPageUserAsync(ComViolationUserPageInputModel input)
    {

        var result = new PageResultModel<QHSEXyViolationModel>();
        
        if (!string.IsNullOrEmpty(input.UserId))
        {
            IQueryable<QHSESysXyViolation> query = _context.Set<QHSESysXyViolation>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query.Where(u => u.ViolationUserId == input.UserId);
            var totalRows = await query.LongCountAsync();
            var violations = await query
                        .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                        .Join(_context.Set<QHSESysOrganization>(),
                            violationUserOrganization => violationUserOrganization.Violation.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (violationUserOrganization, organization) => new {
                                ViolationUserOrganization = violationUserOrganization.Violation,
                                OrganizationName = violationUserOrganization.Organization.NamePath,
                                CheckOrganizationName = organization.NamePath,
                                CheckOrganizationId = organization.OrganizationId
                            })
                        .Select(joinResult => new QHSEXyViolationModel
                        {
                            QhseXyViolationId = joinResult.ViolationUserOrganization.QhseXyViolationId,
                            UserName = joinResult.ViolationUserOrganization.UserName,
                            CheckDate = (DateTime)joinResult.ViolationUserOrganization.CheckDate,
                            OrganizationName = joinResult.OrganizationName,
                            CheckOrganizationName = joinResult.CheckOrganizationName,
                            CheckOrganizationId = joinResult.CheckOrganizationId,
                            WorkItem = joinResult.ViolationUserOrganization.WorkItem,
                            ViolationUserName = joinResult.ViolationUserOrganization.ViolationUserName,
                            Score = (float)joinResult.ViolationUserOrganization.Score,
                            Fine = (float)joinResult.ViolationUserOrganization.Fine,
                            ViolationType = joinResult.ViolationUserOrganization.ViolationType,
                            Description = joinResult.ViolationUserOrganization.Description
                        })
                        .OrderByDescending(u => u.CheckDate)
                        .Page(input.PageNo, input.PageSize)
                        .ToListAsync();
            result = new PageResultModel<QHSEXyViolationModel>
            {
                TotalRows = (int)totalRows,
                Rows = violations,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            result.SetPage(input);
            result.CountTotalPage();
        }
        
        return result;
    }
    public async Task<PageResultModel<QHSEXyViolationModel>> GetComPageUserExAsync(ComViolationUserPageInputModel input)
    {

        var result = new PageResultModel<QHSEXyViolationModel>();

        if (!string.IsNullOrEmpty(input.UserId))
        {
            IQueryable<QHSESysXyViolation> query = _context.Set<QHSESysXyViolation>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query.Where(u => u.ViolationUserId == input.UserId);
            var totalRows = await query.LongCountAsync();
            var violations = await query
                        .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                        .Join(_context.Set<QHSESysOrganization>(),
                            violationUserOrganization => violationUserOrganization.Violation.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (violationUserOrganization, organization) => new {
                                ViolationUserOrganization = violationUserOrganization.Violation,
                                OrganizationName = violationUserOrganization.Organization.NamePath,
                                CheckOrganizationName = organization.NamePath,
                                CheckOrganizationId = organization.OrganizationId
                            })
                        .Select(joinResult => new QHSEXyViolationModel
                        {
                            QhseXyViolationId = joinResult.ViolationUserOrganization.QhseXyViolationId,
                            Check_Type = joinResult.ViolationUserOrganization.Check_Type,
                            UserName = joinResult.ViolationUserOrganization.UserName,
                            CheckDate = (DateTime)joinResult.ViolationUserOrganization.CheckDate,
                            OrganizationName = joinResult.OrganizationName,
                            CheckOrganizationName = joinResult.CheckOrganizationName,
                            WorkItem = joinResult.ViolationUserOrganization.WorkItem,
                            WorkSpecialty = joinResult.ViolationUserOrganization.WorkSpecialty,
                            WorkType = joinResult.ViolationUserOrganization.WorkType,
                            WorkContent = joinResult.ViolationUserOrganization.WorkContent,
                            WorkRisk = joinResult.ViolationUserOrganization.WorkRisk,
                            Location = joinResult.ViolationUserOrganization.Location,
                            ViolationUserName = joinResult.ViolationUserOrganization.ViolationUserName,
                            ViolationUserDepart = joinResult.ViolationUserOrganization.ViolationUserDepart,
                            Position = joinResult.ViolationUserOrganization.Position,
                            Age = joinResult.ViolationUserOrganization.Age,
                            WorkYears = joinResult.ViolationUserOrganization.WorkYears,
                            EmployeeType = joinResult.ViolationUserOrganization.EmployeeType,
                            Score = (float)joinResult.ViolationUserOrganization.Score,
                            Fine = (float)joinResult.ViolationUserOrganization.Fine,
                            ViolationTypeCategory = joinResult.ViolationUserOrganization.ViolationTypeCategory,
                            ViolationType = joinResult.ViolationUserOrganization.ViolationType,
                            ViolationClause = joinResult.ViolationUserOrganization.ViolationClause,
                            Description = joinResult.ViolationUserOrganization.Description,
                            ViolationCategory = joinResult.ViolationUserOrganization.ViolationCategory,
                            HseElement = joinResult.ViolationUserOrganization.HseElement,
                            ObserveComm = joinResult.ViolationUserOrganization.ObserveComm,
                            CauseAnalysis = joinResult.ViolationUserOrganization.CauseAnalysis,
                            DirectDepartment = joinResult.ViolationUserOrganization.DirectDepartment,
                            PotentialConsequence = joinResult.ViolationUserOrganization.PotentialConsequence,
                        })
                        .OrderByDescending(u => u.CheckDate)
                        .Page(input.PageNo, input.PageSize)
                        .ToListAsync();
            result = new PageResultModel<QHSEXyViolationModel>
            {
                TotalRows = (int)totalRows,
                Rows = violations,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            result.SetPage(input);
            result.CountTotalPage();
        }

        return result;
    }
    public async Task<byte[]> UploadExcelUser(ComViolationUserPageInputModel model)
    {
        List<QHSEXyViolationModel> allData = new List<QHSEXyViolationModel>();
        int totalPage = 0;

        // 获取总页数,一次取1000条数据
        model.PageSize = 1000;
        PageResultModel<QHSEXyViolationModel> pageResultModel = await GetComPageUserExAsync(model);
        totalPage = pageResultModel.TotalPage;

        for (int pageNo = 1; pageNo <= totalPage; pageNo++)
        {
            // 设置当前页码
            model.PageNo = pageNo;

            // 执行分页查询，获取当前页的数据
            pageResultModel = await GetComPageUserExAsync(model);
            List<QHSEXyViolationModel> currentPageData = pageResultModel.Rows;

            // 将当前页的数据添加到总数据集合
            allData.AddRange(currentPageData);
        }

        // 设置 ExcelPackage 的 LicenseContext
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        // 创建一个新的Excel包
        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

            // 写入表头并设置字体加粗
            for (int col = 0; col <= 28; col++)
            {
                ExcelRange headerCell = worksheet.Cells[1, col + 1];
                headerCell.Value = GetHeaderTitle(col);
                headerCell.Style.Font.Bold = true;
            }

            // 写入数据
            for (int row = 0; row < allData.Count; row++)
            {
                QHSEXyViolationModel data = allData[row];
                worksheet.Cells[row + 2, 1].Value = data.Check_Type;
                worksheet.Cells[row + 2, 2].Value = data.CheckOrganizationName;
                worksheet.Cells[row + 2, 3].Value = data.UserName;
                worksheet.Cells[row + 2, 4].Value = data.CheckDate.ToString();
                worksheet.Cells[row + 2, 5].Value = data.OrganizationName;
                worksheet.Cells[row + 2, 6].Value = data.WorkItem;
                worksheet.Cells[row + 2, 7].Value = data.WorkSpecialty;
                worksheet.Cells[row + 2, 8].Value = data.WorkType;
                worksheet.Cells[row + 2, 9].Value = data.WorkContent;
                worksheet.Cells[row + 2, 10].Value = data.WorkRisk;
                worksheet.Cells[row + 2, 11].Value = data.Location;
                worksheet.Cells[row + 2, 12].Value = data.ViolationUserName;
                worksheet.Cells[row + 2, 13].Value = data.ViolationUserDepart;
                worksheet.Cells[row + 2, 14].Value = data.Position;
                worksheet.Cells[row + 2, 15].Value = data.Age;
                worksheet.Cells[row + 2, 16].Value = data.WorkYears;
                worksheet.Cells[row + 2, 17].Value = data.EmployeeType;
                worksheet.Cells[row + 2, 18].Value = data.Score;
                worksheet.Cells[row + 2, 19].Value = data.Fine;
                worksheet.Cells[row + 2, 20].Value = data.ViolationTypeCategory;
                worksheet.Cells[row + 2, 21].Value = data.ViolationCategory;
                worksheet.Cells[row + 2, 22].Value = data.ViolationClause;
                worksheet.Cells[row + 2, 23].Value = data.Description;
                worksheet.Cells[row + 2, 24].Value = data.ViolationType;
                worksheet.Cells[row + 2, 25].Value = data.HseElement;
                worksheet.Cells[row + 2, 26].Value = data.ObserveComm;
                worksheet.Cells[row + 2, 27].Value = data.CauseAnalysis;
                worksheet.Cells[row + 2, 28].Value = data.DirectDepartment;
                worksheet.Cells[row + 2, 29].Value = data.PotentialConsequence;
            }

            // 设置列宽
            for (int i = 1; i <= 28; i++)
            {
                worksheet.Column(i).AutoFit();
            }

            // 生成Excel文件的字节数组
            byte[] fileBytes = package.GetAsByteArray();
            return fileBytes;
        }
    }

    public async Task<bool> GetRepeatAsync(QHSEViolationRepeatModel repeatModel)
    {
        var id=await _context.Set<QHSESysXyViolation>().Where(u=>u.CheckDate.Day ==repeatModel.CheckDate.Day && u.OrganizationId == repeatModel.OrganizationId
        && u.ViolationUserName==repeatModel.ViolationUserName && u.Description==repeatModel.Description).Select(u=>u.QhseXyViolationId).FirstOrDefaultAsync();
        if(id != null)
        {
            return true;
        }
        return false;
    }
}
